var mysql=require('mysql');
//建立数据库连接
var db=mysql.createConnection({
    host     : 'localhost',
    user     : 'root',
    password : 'root',
    database : 'tiantianpeiwan'
});

var json={
    login:function (user,pass,fn) {
        var sql=`SELECT * FROM p_login WHERE (L_user="${user}" OR L_tel="${user}") AND L_pass="${pass}"`;

        db.query(sql,function (err,data) {
            fn(err,data)
        })
    },
   register:function (user,fn) {
       var sql=`select * from p_login where L_user="${user}" OR L_tel="${user}"`;

       db.query(sql,function (err,data) {
           fn(err,data)
       })
   },
    add:function (user,pass,tel,fn) {
        var sql=`insert into p_login(L_user,L_pass,L_tel) value("${user}","${pass}","${tel}")`;
      db.query(sql,function (err,data) {
          fn(err,data)
      })
    },
    record:function (id,fn) {
        var sql=`SELECT * FROM (p_gift LEFT JOIN p_giftattribute ON GF_gagift=GA_id  ) LEFT JOIN p_user ON U_id= GF_puid WHERE GF_puid="${id}";`;
        db.query(sql,function (err,data) {

            fn(err,data)
        })
    },
    send:function (id,fn) {
        var sql=`SELECT * FROM (p_gift LEFT JOIN p_giftattribute ON GF_gagift=GA_id  ) LEFT JOIN p_user ON U_id= GF_uid WHERE GF_uid="${id}";`;
        db.query(sql,function (err,data) {

            fn(err,data)
        })
    },
    personal:function (id,fn) {
        var sql=`SELECT * FROM p_user WHERE U_id="${id}"`;
        db.query(sql,function (err,data) {
            fn(err,data)
        })
    },
    person:function (id,src,nickname,qq,fn) {
        var sql=`UPDATE	p_user SET U_photo="${src}",U_falsename="${nickname}",U_qq="${qq}" WHERE U_id="${id}" `;
        db.query(sql,function (err,data) {
            fn(err,data)
        })
    },
    safe:function (id,fn) {
        var sql=`SELECT * FROM p_user WHERE U_id="${id}"`;
        db.query(sql,function (err,data) {
            fn(err,data)
        })
    },
    apply:function (id,fn) {
        var sql=`SELECT * FROM p_user WHERE U_id="${id}"`;
        db.query(sql,function (err,data) {
            fn(err,data)
        })
    },
    findID:function (user,fn) {
        var sql=`SELECT * FROM p_login WHERE L_user="${user}" OR L_tel="${user}"`;
        db.query(sql,function (err,data) {
            fn(err,data)
        })
    },
    alter:function (phone,pass,fn) {
        var sql=`UPDATE  p_login L_pass="${pass} WHERE L_tel="${phone}"`;
        db.query(sql,function (err,data) {
            fn(err,data)
        })
    },
    passwd:function (id,old,newPass,fn) {
        var sql=`UPDATE  p_login L_pass="${newPass} WHERE L_id="${id}" AND L_pass="${old}"`;
        db.query(sql,function (err,data) {
            fn(err,data)
        })
    },
   user:function (user,fn) {
        var sql=`SELECT * FROM p_login WHERE L_user="${user}" OR L_tel="${user}"`;
        db.query(sql,function (err,data) {
            fn(err,data)
        })
    },

};

module.exports=json;